from sqlalchemy import and_, select
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy.orm import joinedload

from app.database.models import Category, Server


async def get_category(
    session: AsyncSession, category_id: int, joined: bool = False
) -> Category:

    q = select(Category).where(Category.id == category_id)

    if joined:
        q = q.options(joinedload(Category.sites))

    res = await session.execute(q)

    return res.scalar()


async def get_category_for_private(
    session: AsyncSession, country_id: int, provider_id: int
) -> Category:

    q = (
        select(Category)
        .join(Category.servers)
        .where(
            and_(
                Server.proxy_privacy == "private",
                Server.country_id == country_id,
                Server.provider_id == provider_id,
            )
        )
    )

    res = await session.execute(q)

    return res.scalar()


async def get_category_by_name(session: AsyncSession, name: str) -> Category:

    q = select(Category).where(Category.name == name)
    res = await session.execute(q)

    return res.scalar()


async def query_categories(
    session: AsyncSession,
    proxy_type: str,
    proxy_privacy: str,
    country_id: int,
    provider_id: int,
) -> list[Category]:

    q = (
        select(Category)
        .join(Category.servers)
        .filter(
            and_(
                Server.status == "active",
                Server.proxy_type == proxy_type,
                Server.proxy_privacy == proxy_privacy,
                Server.country_id == country_id,
                Server.provider_id == provider_id,
            )
        )
    )

    res = await session.execute(q)

    return res.scalars().unique().all()
